#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
涨停板数据表模型 (FBoard)
"""

from sqlalchemy import Column, Integer, String, Float, Text, Index
from typing import List
import pandas as pd

from .BaseModel import BaseModel, safe_convert_numeric, safe_convert_int, safe_get_field_value, standardize_date_format

class FBoard(BaseModel):
    """涨停板数据表模型"""
    __tablename__ = 'fboard'
    
    # 列名映射（CSV列名 -> 数据库字段名）
    aliases = {
        # 中文列名映射
        '交易日': 'trade_date',
        '代码': 'security_id',
        '名称': 'security_name',
        '类型': 'type',
        '收盘价': 'close_price',
        '开盘价': 'open_price',
        '最高价': 'high_price',
        '最低价': 'low_price',
        '成交量': 'volume',
        '量比': 'volume_ratio',
        '板块': 'sector',
        '连板次数': 'consecutive_limit_up',
        '打开涨停': 'limit_up_opened',
        '最近封涨时间': 'last_limit_up_time',
        '当日涨幅': 'daily_change',
        '流通市值(亿)': 'market_cap',
        '净流入额(万元)': 'net_inflow',
        '换手率': 'turnover_rate',
        '成交额(亿元)': 'turnover_amount',
        '大单流入额(亿元)': 'big_order_inflow',
        '热点概念': 'hot_concepts',
        '集合竞价成交额(亿元)': 'auction_amount',
        '昨收价': 'prev_close',
        '开-收涨幅': 'open_close_change',
        '炸板亏幅': 'limit_up_loss',
        '首次涨停时间': 'first_limit_up_time',
        # 英文列名映射（保持兼容性）
        'tradeDate': 'trade_date',
        'securityId': 'security_id',
        'market': 'market',
        'securityName': 'security_name',
        'type': 'type',
        'closePrice': 'close_price',
        'openPrice': 'open_price',
        'highPrice': 'high_price',
        'lowPrice': 'low_price',
        'volume': 'volume',
        'volumeRatio': 'volume_ratio',
        'sector': 'sector',
        'consecutiveLimitUp': 'consecutive_limit_up',
        'limitUpOpened': 'limit_up_opened',
        'lastLimitUpTime': 'last_limit_up_time',
        'dailyChange': 'daily_change',
        'marketCap': 'market_cap',
        'netInflow': 'net_inflow',
        'turnoverRate': 'turnover_rate',
        'turnoverAmount': 'turnover_amount',
        'bigOrderInflow': 'big_order_inflow',
        'hotConcepts': 'hot_concepts',
        'auctionAmount': 'auction_amount',
        'prevClose': 'prev_close',
        'openCloseChange': 'open_close_change',
        'limitUpLoss': 'limit_up_loss',
        'firstLimitUpTime': 'first_limit_up_time'
    }
    
    # 复合主键字段
    trade_date = Column(String(10), primary_key=True, comment='交易日期')  # 交易日期
    security_id = Column(String(20), primary_key=True, comment='股票代码')  # 股票代码
    market = Column(Integer, primary_key=True, comment='市场类型')  # 市场类型：1-上海，2-深圳
    type = Column(String(20), primary_key=True, comment='涨停类型')  # 涨停类型：连板、首板等
    
    # 基本信息字段
    security_name = Column(String(100), comment='股票名称')  # 股票名称
    close_price = Column(Float, comment='收盘价')  # 收盘价
    open_price = Column(Float, comment='开盘价')  # 开盘价
    high_price = Column(Float, comment='最高价')  # 最高价
    low_price = Column(Float, comment='最低价')  # 最低价
    prev_close = Column(Float, comment='昨收价')  # 昨收价
    
    # 成交量和价格相关字段
    volume = Column(Integer, comment='成交量')  # 成交量
    volume_ratio = Column(Float, comment='量比')  # 量比
    turnover_amount = Column(Float, comment='成交金额(亿元)')  # 成交金额(亿元)
    turnover_rate = Column(Float, comment='换手率(%)')  # 换手率(%)
    auction_amount = Column(Float, comment='集合竞价成交金额(亿元)')  # 集合竞价成交金额(亿元)
    
    # 涨停相关字段
    consecutive_limit_up = Column(Integer, comment='连板次数')  # 连板次数
    limit_up_opened = Column(String(10), comment='是否打开涨停')  # 是否打开涨停：是/否
    first_limit_up_time = Column(String(20), comment='首次涨停时间')  # 首次涨停时间
    last_limit_up_time = Column(String(20), comment='最近封涨时间')  # 最近封涨时间
    limit_up_loss = Column(Float, comment='炸板亏幅(%)')  # 炸板亏幅(%)
    
    # 涨跌幅相关字段
    daily_change = Column(Float, comment='当日涨幅(%)')  # 当日涨幅(%)
    open_close_change = Column(Float, comment='开-收涨幅(%)')  # 开-收涨幅(%)
    
    # 资金流向相关字段
    net_inflow = Column(Float, comment='净流入额(万元)')  # 净流入额(万元)
    big_order_inflow = Column(Float, comment='大单流入额(亿元)')  # 大单流入额(亿元)
    
    # 市值和板块相关字段
    market_cap = Column(Float, comment='流通市值(亿元)')  # 流通市值(亿元)
    sector = Column(String(50), comment='所属板块')  # 所属板块
    hot_concepts = Column(Text, comment='热点概念')  # 热点概念
    
    # 索引
    __table_args__ = (
        # 复合主键索引（trade_date, security_id, market, type）
        Index('idx_fboard_primary', 'trade_date', 'security_id', 'market', 'type'),
        # 单字段索引
        Index('idx_fboard_trade_date', 'trade_date'),
        Index('idx_fboard_security_id', 'security_id'),
        Index('idx_fboard_market', 'market'),
        Index('idx_fboard_type', 'type'),
        # 常用查询组合索引
        Index('idx_fboard_trade_date_type', 'trade_date', 'type'),
        Index('idx_fboard_consecutive_limit_up', 'consecutive_limit_up'),
    )

    @classmethod
    def get_session(cls):
        """获取数据库会话"""
        return cls._db_manager.get_stock_data_session()
    
    @classmethod
    def _get_existing_record(cls, session, data):
        """获取现有记录"""
        return session.query(cls).filter(
            cls.trade_date == data['trade_date'],
            cls.security_id == data['security_id'],
            cls.market == data['market'],
            cls.type == data['type']
        ).first()
    
    @classmethod
    def _update_existing_record(cls, existing, data):
        """更新现有记录"""
        existing.security_name = data.get('security_name', existing.security_name)
        existing.type = data.get('type', existing.type)
        existing.close_price = data.get('close_price', existing.close_price)
        existing.open_price = data.get('open_price', existing.open_price)
        existing.high_price = data.get('high_price', existing.high_price)
        existing.low_price = data.get('low_price', existing.low_price)
        existing.volume = data.get('volume', existing.volume)
        existing.volume_ratio = data.get('volume_ratio', existing.volume_ratio)
        existing.sector = data.get('sector', existing.sector)
        existing.consecutive_limit_up = data.get('consecutive_limit_up', existing.consecutive_limit_up)
        existing.limit_up_opened = data.get('limit_up_opened', existing.limit_up_opened)
        existing.last_limit_up_time = data.get('last_limit_up_time', existing.last_limit_up_time)
        existing.daily_change = data.get('daily_change', existing.daily_change)
        existing.market_cap = data.get('market_cap', existing.market_cap)
        existing.net_inflow = data.get('net_inflow', existing.net_inflow)
        existing.turnover_rate = data.get('turnover_rate', existing.turnover_rate)
        existing.turnover_amount = data.get('turnover_amount', existing.turnover_amount)
        existing.big_order_inflow = data.get('big_order_inflow', existing.big_order_inflow)
        existing.hot_concepts = data.get('hot_concepts', existing.hot_concepts)
        existing.auction_amount = data.get('auction_amount', existing.auction_amount)
        existing.prev_close = data.get('prev_close', existing.prev_close)
        existing.open_close_change = data.get('open_close_change', existing.open_close_change)
        existing.limit_up_loss = data.get('limit_up_loss', existing.limit_up_loss)
        existing.first_limit_up_time = data.get('first_limit_up_time', existing.first_limit_up_time)
    
    @classmethod
    def _to_dict(cls, result):
        """将结果转换为字典"""
        return {
            'trade_date': result.trade_date,
            'security_id': result.security_id,
            'market': result.market,
            'security_name': result.security_name,
            'type': result.type,
            'close_price': result.close_price,
            'open_price': result.open_price,
            'high_price': result.high_price,
            'low_price': result.low_price,
            'volume': result.volume,
            'volume_ratio': result.volume_ratio,
            'sector': result.sector,
            'consecutive_limit_up': result.consecutive_limit_up,
            'limit_up_opened': result.limit_up_opened,
            'last_limit_up_time': result.last_limit_up_time,
            'daily_change': result.daily_change,
            'market_cap': result.market_cap,
            'net_inflow': result.net_inflow,
            'turnover_rate': result.turnover_rate,
            'turnover_amount': result.turnover_amount,
            'big_order_inflow': result.big_order_inflow,
            'hot_concepts': result.hot_concepts,
            'auction_amount': result.auction_amount,
            'prev_close': result.prev_close,
            'open_close_change': result.open_close_change,
            'limit_up_loss': result.limit_up_loss,
            'first_limit_up_time': result.first_limit_up_time
        }
    
    @classmethod
    def get_limit_up_stocks(cls, trade_date: str) -> List['FBoard']:
        """获取指定日期的涨停股票"""
        if not cls._db_manager:
            raise ValueError("数据库管理器未设置，请先调用 set_db_manager()")
        
        try:
            session = cls.get_session()
            results = session.query(cls).filter(
                cls.trade_date == trade_date
            ).all()
            session.close()
            return results
        except Exception as e:
            # 获取涨停股票失败: {str(e)}
            session.close()
            return []
    
    @classmethod
    def import_from_dataframe(cls, df):
        """从DataFrame导入数据"""
        if not cls._db_manager:
            raise ValueError("数据库管理器未设置，请先调用 set_db_manager()")
        
        try:
            session = cls.get_session()
            success_count = 0
            
            for _, row in df.iterrows():
                try:
                    # 从证券代码中提取市场信息
                    security_id = str(row['security_id'])
                    if security_id.endswith('.SZ'):
                        market = 2  # 深圳
                        security_id = security_id.replace('.SZ', '')
                    elif security_id.endswith('.SH'):
                        market = 1  # 上海
                        security_id = security_id.replace('.SH', '')
                    else:
                        market = 1  # 默认为上海
                        
                    type = row['type']
                    # 标准化日期格式
                    trade_date = standardize_date_format(row['trade_date'])
                    if not trade_date:
                        continue
                    
                    # 检查是否已存在相同记录
                    existing = session.query(cls).filter(
                        cls.trade_date == trade_date,
                        cls.security_id == security_id,
                        cls.market == market,
                        cls.type == type
                    ).first()
                    
                    if existing:
                        # 更新现有记录的所有字段，使用安全转换
                        if 'security_name' in row and not pd.isna(row['security_name']):
                            existing.security_name = str(row['security_name'])
                        if 'type' in row and not pd.isna(row['type']):
                            existing.type = str(row['type'])
                        if 'close_price' in row and not pd.isna(row['close_price']):
                            existing.close_price = safe_convert_numeric(row['close_price'])
                        if 'open_price' in row and not pd.isna(row['open_price']):
                            existing.open_price = safe_convert_numeric(row['open_price'])
                        if 'high_price' in row and not pd.isna(row['high_price']):
                            existing.high_price = safe_convert_numeric(row['high_price'])
                        if 'low_price' in row and not pd.isna(row['low_price']):
                            existing.low_price = safe_convert_numeric(row['low_price'])
                        if 'prev_close' in row and not pd.isna(row['prev_close']):
                            existing.prev_close = safe_convert_numeric(row['prev_close'])
                        if 'volume' in row and not pd.isna(row['volume']):
                            existing.volume = safe_convert_int(row['volume'])
                        if 'volume_ratio' in row and not pd.isna(row['volume_ratio']):
                            existing.volume_ratio = safe_convert_numeric(row['volume_ratio'])
                        if 'sector' in row and not pd.isna(row['sector']):
                            existing.sector = str(row['sector'])
                        if 'consecutive_limit_up' in row and not pd.isna(row['consecutive_limit_up']):
                            # 连板次数自动+1，首板为0，1连板为1，2连板为2，以此类推
                            raw_value = safe_convert_int(row['consecutive_limit_up'])
                            existing.consecutive_limit_up = raw_value + 1 if raw_value is not None else 0
                        if 'limit_up_opened' in row and not pd.isna(row['limit_up_opened']):
                            existing.limit_up_opened = str(row['limit_up_opened'])
                        if 'last_limit_up_time' in row and not pd.isna(row['last_limit_up_time']):
                            existing.last_limit_up_time = str(row['last_limit_up_time'])
                        if 'daily_change' in row and not pd.isna(row['daily_change']):
                            existing.daily_change = safe_convert_numeric(row['daily_change'])
                        if 'market_cap' in row and not pd.isna(row['market_cap']):
                            existing.market_cap = safe_convert_numeric(row['market_cap'])
                        if 'net_inflow' in row and not pd.isna(row['net_inflow']):
                            existing.net_inflow = safe_convert_numeric(row['net_inflow'])
                        if 'turnover_rate' in row and not pd.isna(row['turnover_rate']):
                            existing.turnover_rate = safe_convert_numeric(row['turnover_rate'])
                        if 'turnover_amount' in row and not pd.isna(row['turnover_amount']):
                            existing.turnover_amount = safe_convert_numeric(row['turnover_amount'])
                        if 'big_order_inflow' in row and not pd.isna(row['big_order_inflow']):
                            existing.big_order_inflow = safe_convert_numeric(row['big_order_inflow'])
                        if 'hot_concepts' in row and not pd.isna(row['hot_concepts']):
                            existing.hot_concepts = str(row['hot_concepts'])
                        if 'auction_amount' in row and not pd.isna(row['auction_amount']):
                            existing.auction_amount = safe_convert_numeric(row['auction_amount'])
                        if 'prev_close' in row and not pd.isna(row['prev_close']):
                            existing.prev_close = safe_convert_numeric(row['prev_close'])
                        if 'open_close_change' in row and not pd.isna(row['open_close_change']):
                            existing.open_close_change = safe_convert_numeric(row['open_close_change'])
                        if 'limit_up_loss' in row and not pd.isna(row['limit_up_loss']):
                            existing.limit_up_loss = safe_convert_numeric(row['limit_up_loss'])
                        if 'first_limit_up_time' in row and not pd.isna(row['first_limit_up_time']):
                            existing.first_limit_up_time = str(row['first_limit_up_time'])
                    else:
                        # 创建新记录，使用安全转换
                        fboard = cls(
                            trade_date=trade_date,
                            security_id=security_id,
                            market=market,
                            security_name=str(row.get('security_name', '')),
                            type=str(row.get('type', '')),
                            close_price=safe_convert_numeric(row.get('close_price')),
                            open_price=safe_convert_numeric(row.get('open_price')),
                            high_price=safe_convert_numeric(row.get('high_price')),
                            low_price=safe_convert_numeric(row.get('low_price')),
                            prev_close=safe_convert_numeric(row.get('prev_close')),
                            volume=safe_convert_int(row.get('volume')),
                            volume_ratio=safe_convert_numeric(row.get('volume_ratio')),
                            turnover_amount=safe_convert_numeric(row.get('turnover_amount')),
                            turnover_rate=safe_convert_numeric(row.get('turnover_rate')),
                            auction_amount=safe_convert_numeric(row.get('auction_amount')),
                            consecutive_limit_up=safe_convert_int(row.get('consecutive_limit_up', 0)) + 1,  # 连板次数自动+1
                            limit_up_opened=str(row.get('limit_up_opened', '')),
                            first_limit_up_time=str(row.get('first_limit_up_time', '')),
                            last_limit_up_time=str(row.get('last_limit_up_time', '')),
                            limit_up_loss=safe_convert_numeric(row.get('limit_up_loss')),
                            daily_change=safe_convert_numeric(row.get('daily_change')),
                            open_close_change=safe_convert_numeric(row.get('open_close_change')),
                            net_inflow=safe_convert_numeric(row.get('net_inflow')),
                            big_order_inflow=safe_convert_numeric(row.get('big_order_inflow')),
                            market_cap=safe_convert_numeric(row.get('market_cap')),
                            sector=str(row.get('sector', '')),
                            hot_concepts=str(row.get('hot_concepts', ''))
                        )
                        session.add(fboard)
                    
                    success_count += 1
                    
                except Exception as e:
                    # 处理涨停板数据失败: {str(e)}
                    continue
            
            session.commit()
            session.close()
            return success_count
            
        except Exception as e:
            # 导入涨停板数据失败: {str(e)}
            session.rollback()
            session.close()
            return 0
